package com.itogis.rest.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.itogis.rest.datasource.DataSourceManager;

public class BaseDao {

	Connection cn;
	Statement st;
	ResultSet rs;

	public Connection getConnection() {
		return DataSourceManager.getConnection();
	}

	public void closeConnection(ResultSet rs, Statement st, Connection cn) {
		DataSourceManager.closeConnection(rs, st, cn);
	}

	public List find(String sql) {
		try {
			cn = this.getConnection();
			st = cn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				rs.getObject(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DataSourceManager.closeConnection(rs, st, cn);
		}
		return null;
	}

	public int findIntId(String sql) {
		try {
			cn = this.getConnection();
			st = cn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DataSourceManager.closeConnection(rs, st, cn);
		}
		return -1;
	}

	public String findStringId(String sql) {
		try {
			cn = this.getConnection();
			st = cn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				return rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DataSourceManager.closeConnection(rs, st, cn);
		}
		return null;
	}
    //执行存储过程通用方法
	public void proceduresExePublic(Map<String, String> input,
			Map<String, String> outPut) throws Exception {
		String procedureName = input.get("procedureName");
		int outLength = Integer.valueOf(input.get("outLength")).intValue();
		Connection conn = this.getConnection();
		CallableStatement cstmt = null;
		String sql = "{call " + procedureName + "(";
		try {
			for (int i = 0; i < input.size() - 2; i++) {
				sql = sql + "?,";
			}
			if (input.size() - 2 < 1 && outLength == 0) {// 无入参
				// 无出参
				sql = sql + ")}";
			} else if (input.size() - 2 > 1 && outLength == 0) {// 有入参
				// 无出参
				sql = sql.substring(0, sql.length() - 1) + ")}";
			} else {// 有出参
				for (int i = 1; i <= outLength; i++) {
					if (i == outLength) {
						sql = sql + "?)}";
					} else {
						sql = sql + "?,";
					}
				}
			}
			// System.out.println("sql=" + sql);
			cstmt = conn.prepareCall(sql);
			for (int i = 1; i <= input.size() - 2 + outLength; i++) {
				if (i <= input.size() - 2) {
					cstmt.setString(i, input.get(String.valueOf(i)));
				} else {
					cstmt.registerOutParameter(i, Types.VARCHAR);
				}
			}
			cstmt.execute();
			for (int i = 1; i <= outLength; i++) {
				outPut.put(String.valueOf(i), cstmt.getString(input.size() - 2
						+ i));
			}
			cstmt.close();
		} catch (Exception ex) {
			throw ex;
		} finally {
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	//获取JDBC链接
	public static Connection getJdbcConnection() throws Exception {
		String dbAddress = "";
		String dbUserName = "";
		String DBpass = "";
		String driver = "";
		dbAddress = "jdbc:oracle:thin:@135.224.85.5:1521:xjods";
		dbUserName = "xjintegral";
		DBpass = "inteSysE2";
		
		dbAddress="jdbc:oracle:thin:@127.0.0.1:1522:orcl";
		dbUserName = "system";
		DBpass = "1";
		driver = "oracle.jdbc.driver.OracleDriver";
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(dbAddress, dbUserName, DBpass);
		} catch (Exception e) {
			throw new Exception(e);
		}
		return conn;
	}
	//根据查询打印TABLE
	public String selectSQLToPrintTable(String sql) throws Exception {
		//"<style>	table {border-collapse:collapse;border:1px solid #EEE;font-size:14px;}	.row_head{background:#EEE;border-bottom:1px solid #CCC;padding:4px; font-size:16px;}	td{border:1px solid #DDD;padding:4px;}</style>";
		Connection conn = this.getConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String colName = "";
		String dataView = "";
		String tableView = "";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			int g = ps.getResultSet().getMetaData().getColumnCount();
			for (int i = 0; i < g; i++) {
				colName = colName + "<td nowrap class=\"head\" >";
				colName = colName
						+ ps.getResultSet().getMetaData().getColumnName(i + 1);
				colName = colName + "</td> \n";
			}
			if (!colName.equals("")) {
				colName = "<tr class=\"row_head\">\n" + colName + "</tr> \n";
			}
			if (rs != null) {
				while (rs.next()) {
					dataView = dataView
							+ "<tr align=\"center\"  class=\"row_even\" >\n";
					for (int i = 0; i < g; i++) {

						dataView = dataView
								+ "<td  align=\"left\" nowrap class=\"tablecell\">";
						if (rs.getString(i + 1) == null) {
							dataView = dataView + "";
						} else {
							dataView = dataView + rs.getString(i + 1);
						}
						dataView = dataView + "</td> \n";
					}
					dataView = dataView + "</tr>\n";
				}
			}
			if (!dataView.equals("")) {
				tableView = "<table class=\"detail\" width=\"60%\" border=\"1\" id=\"bottom_Color_change\">\n"
						+ colName + dataView + "</table>";
			}
		} catch (Exception ex) {
			// throw new ApplicationException(
			// ErrorCode.ParameterError.UNKNOW_ERROR, "生成监控详单文本信息异常!");
		} finally {
			try { // 关闭 rs
				if (rs != null) {
					rs.close();
					rs = null;
				}
				// 关闭ps
				if (ps != null) {
					ps.close();
					ps = null;
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return tableView;
	}
	//查询返回list map
	public List<Map<String, String>> selectSQLListMapResult(String sql) throws Exception {
		Connection conn = this.getConnection();		
		//Connection conn = getJdbcConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<Map<String, String>> out =new ArrayList<Map<String, String>>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			int g = ps.getResultSet().getMetaData().getColumnCount();				
			if (rs != null) {
				while (rs.next()) {					
					Map<String, String> rowData = new LinkedHashMap<String, String>();
					for (int i = 0; i < g; i++) {						
						//System.out.println("字段名:"+ps.getResultSet().getMetaData().getColumnName(i + 1)+"  字段类型:"+ps.getResultSet().getMetaData().getColumnTypeName(i + 1)+" 字段值:"+rs.getString(i + 1));
						rowData.put(ps.getResultSet().getMetaData().getColumnName(i + 1), rs.getString(i + 1));				
					}
					out.add(rowData);
				}
			}			
		} catch (Exception ex) {
			// throw new ApplicationException(
			// ErrorCode.ParameterError.UNKNOW_ERROR, "生成监控详单文本信息异常!");
		} finally {
			try { // 关闭 rs
				if (rs != null) {
					rs.close();
					rs = null;
				}
				// 关闭ps
				if (ps != null) {
					ps.close();
					ps = null;
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return out;
	}
	
	public static void main(String[] args) throws Exception {
		BaseDao run = new BaseDao();
		List<Map<String, String>> listMap=run.selectSQLListMapResult("select t.* from sys_userinfo t");
		System.out.println("行数:"+listMap.size());
		Map<String, String> oneMap=listMap.get(0);		
		System.out.println("取第一行某个值:"+oneMap.get("NAME"));
		for(Map<String, String> map1:listMap)
		{	
			for(Map.Entry<String, String> entry: map1.entrySet()) { 
				 System.out.println(entry.getKey() + ":" + entry.getValue() + "\t"); 
			} 
		}
		
//		Map<String, String> input = new LinkedHashMap<String, String>();
//		Map<String, String> outPut = new LinkedHashMap<String, String>();
//		/*
//		 * 存储过程参数顺序必须是先入参后是出参;入参传入顺序、出参传入顺序与存储过程参数顺序一致;存储过程出参必须赋值 不然程序报错
//		 */
//		// 存储过程名称procedureName 不带参数的过程名称
//		input.put("procedureName", "PRO_TEST");
//		// 出参个数 出参key值也是从1开始
//		input.put("outLength", "3");
//		// 存储过程入参 key 从1开始必须连续，多少个入参没关系
//		input.put("1", "1");
//		input.put("2", "2");
//		input.put("3", "3");
//		input.put("4", "4");
//		input.put("5", "5");
//		input.put("6", "6");
//		input.put("7", "Product");
//		
//		run.exePublicProcedures(input, outPut);
	}
}
